﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using Newtonsoft.Json;
using OfficeOpenXml;
using System.Data;
using Compass.Wasm.Shared.Extensions;

namespace Compass.Test;

public class ReadExcel
{

    /// <summary>
    /// 使用 C# 和 OpenXML 读取大型 Excel 文件
    /// </summary>
    public void ReadByOpenXml()
    {
        //excel地址
        var filePath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory), "Shortage list 20241111.xlsx");


        using var doc = SpreadsheetDocument.Open(filePath, false);

        var workbookPart = doc.WorkbookPart;
        var sheet = workbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);

        var reader = OpenXmlReader.Create(worksheetPart);
        while (reader.Read())
        {
            if (reader.ElementType == typeof(Row))
            {
                var row = (Row)reader.LoadCurrentElement();
                foreach (var cell in row.Elements<Cell>())
                {
                    var cellValue = GetCellValue(doc, cell);
                    Console.Write(cellValue + " ");
                }

                Console.WriteLine();
            }
        }

        static string GetCellValue(SpreadsheetDocument doc, Cell cell)
        {
            var stringTablePart = doc.WorkbookPart.SharedStringTablePart;
            var value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }

            return value;
        }
    }

    public void ReadByEPPlus()
    {
        //excel地址
        var shortagePath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory), "Shortage list 20241112.xlsx");
        var inventoryPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory), "Item List HALTON_BONNY.JIA 2024-11-01T01_21_41.xlsx");

        //https://www.thecodebuzz.com/read-write-excel-in-dotnet-core-epplus/
        //https://epplussoftware.com/developers/licenseexception
        //https://www.cnblogs.com/dzw2017/p/6663714.html
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        var columnName = "AD";//给变动的列赋值
        ChangeShortageTitle(shortagePath, columnName.ToUpper());
        ChangeInventoryTitle(inventoryPath);

        var shortageDatas = ReadFromExcel<List<ShortageDto>>(shortagePath);
        var inventoryStrings = ReadFromExcel<List<InventoryString>>(inventoryPath);

        var shortages = shortageDatas.Where(x => (!string.IsNullOrEmpty(x.Shortage)) && double.Parse(x.Shortage) < 0);

        foreach (var shortage in shortages)
        {
            var inventoryData = inventoryStrings.FirstOrDefault(x => x.Number == shortage.Number);
            if (inventoryData is null) continue;
            shortage.BU = inventoryData.BU;
            shortage.Consumable = inventoryData.Consumable;
        }

        var fsShortages = shortages.Where(x => ((!string.IsNullOrEmpty(x.Consumable)) && x.Consumable=="0")&&((!string.IsNullOrEmpty(x.BU))&&(x.BU.ToUpper()=="FS" || x.BU.ToUpper()=="SHARE"))).ToList();



        //写入新的excel
        var newFilePath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory), $"【Compass】ShortageList{DateTime.Now:yyyy-MM-dd-hh-mm-ss}.xlsx");
        if (fsShortages.Any())
        {
            WriteToExcel(fsShortages, newFilePath);
        }
        else
        {
            Console.WriteLine("没有数据？");
        }

        void ChangeShortageTitle(string path, string colName)
        {
            using var package = new ExcelPackage(path);

            //修改表头,方便后面读取数据
            //Epplus的所有行列数都是以1开始的
            var worksheet = package.Workbook.Worksheets[0];//第一张表格
                                                           //直接指定单元格进行赋值
            worksheet.Cells["A1"].Value = "Number";
            worksheet.Cells["B1"].Value = "Description";
            worksheet.Cells["C1"].Value = "Unit";
            worksheet.Cells["D1"].Value = "SafetyStock";
            worksheet.Cells["E1"].Value = "Vendor";
            worksheet.Cells["F1"].Value = "LeadTime";
            worksheet.Cells["G1"].Value = "OrderMultiple";
            worksheet.Cells["H1"].Value = "MainInventory";
            worksheet.Cells["I1"].Value = "WSInventory";
            worksheet.Cells["J1"].Value = "Inventory";
            worksheet.Cells[$"{colName}1"].Value = "Shortage";
            // 保存Excel文件
            package.Save();
        }
        void ChangeInventoryTitle(string path)
        {
            using var package = new ExcelPackage(path);

            //修改表头,方便后面读取数据
            //Epplus的所有行列数都是以1开始的
            var worksheet = package.Workbook.Worksheets[0];//第一张表格
                                                           //直接指定单元格进行赋值
            worksheet.Cells["A1"].Value = "Number";
            worksheet.Cells["J1"].Value = "BU";
            worksheet.Cells["AC1"].Value = "Consumable";

            // 保存Excel文件
            package.Save();
        }
    }

    public void ReadInvValuation()
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        //excel地址
        var InventoryValuationPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory), "Inventory Valuation 20241031.xlsx");

        using var package = new ExcelPackage(InventoryValuationPath);
        var worksheet = package.Workbook.Worksheets[0];//第一张表格

        //数据清理：判断单元格的值，删除行
        //①，删除前11行
        if (worksheet.Cells[1, 1].Value.ToString() == "Inventory Valuation")
        {

            worksheet.DeleteRow(1, 11);
        }
        //②，遍历表格，删除无用的行(description,第二列)
        var totalRows = worksheet.Dimension.End.Row;
        for (var i = 1; i < totalRows+1; i++)
        {
            if (worksheet.Cells[i, 2].Value==null || string.IsNullOrEmpty(worksheet.Cells[i, 2].Value.ToString()))
            {
                worksheet.DeleteRow(i);
                i--;
                totalRows -= 1;
            }
        }
        //③取消筛选，取消合并单元格
        worksheet.Cells[worksheet.Dimension.Address].AutoFilter=false;
        //worksheet.Cells["A1:AC1"].Merge = false;

        //④，重命名表头，使用的值也命名一下，否则读取的数据会错位
        worksheet.Cells["A1"].Value = "Number";
        worksheet.Cells["B1"].Value = "Description";
        worksheet.Cells["C1"].Value = "Unit";
        worksheet.Cells["D1"].Value = "BU";
        //worksheet.Cells["E1"].Value = "X1";
        //worksheet.Cells["F1"].Value = "X2";
        worksheet.Cells["G1"].Value = "AsOfQuantity";
        worksheet.Cells["H1"].Value = "AsOfValue";
        worksheet.Cells["I1"].Value = "IncreaseQuantity";
        worksheet.Cells["J1"].Value = "IncreaseValue";
        worksheet.Cells["K1"].Value = "DecreasesQuantity";
        worksheet.Cells["L1"].Value = "DecreasesValue";
        //worksheet.Cells["M1"].Value = "X3";
        worksheet.Cells["N1"].Value = "LastQuantity";
        worksheet.Cells["O1"].Value = "LastValue";
        worksheet.Cells["P1"].Value = "CostPostedToGL";
        //worksheet.Cells["Q1"].Value = "X4";
        //worksheet.Cells["R1"].Value = "X5";
        worksheet.Cells["S1"].Value = "MainCost";
        //worksheet.Cells["T1"].Value = "X6";
        worksheet.Cells["U1"].Value = "MainValue";
        //worksheet.Cells["V1"].Value = "X7";
        //worksheet.Cells["W1"].Value = "X8";
        //worksheet.Cells["X1"].Value = "X9";
        //worksheet.Cells["Y1"].Value = "X10";
        worksheet.Cells["Z1"].Value = "WsCost";
        worksheet.Cells["AA1"].Value = "WsValue";
        worksheet.Cells["AB1"].Value = "SbuCost";
        worksheet.Cells["AC1"].Value = "SbuValue";
        //把一些重复的表头重命名一下，放置读取数据出错

        package.Save();

        //⑤，读取数据
        var inventoryValuationStrings = ReadFromExcel<List<InventoryValuationString>>(InventoryValuationPath);

        var inventoryValuationDtos = inventoryValuationStrings.Where(
                x => !string.IsNullOrEmpty(x.IncreaseQuantity) ||
                   !string.IsNullOrEmpty(x.DecreasesQuantity))
            .Select(x => new InventoryValuationDto
            {
                Number = x.Number,
                Description = x.Description,
                Unit = x.Unit,
                BU = x.BU,
                IncreaseQuantity = x.IncreaseQuantity.ToDouble(),
                DecreasesQuantity = x.DecreasesQuantity.ToDouble(),
                LastQuantity = x.LastQuantity.ToDouble(),
                LastValue = x.LastValue.ToDouble()
            });
    }

    public T ReadFromExcel<T>(string path, bool hasHeader = true)
    {
        using (var excelPack = new ExcelPackage())
        {
            //Load excel stream
            using (var stream = File.OpenRead(path))
            {
                excelPack.Load(stream);
            }

            //Lets Deal with first worksheet.(You may iterate here if dealing with multiple sheets)
            var ws = excelPack.Workbook.Worksheets[0];

            //Get all details as DataTable -because Datatable make life easy :)
            DataTable excelasTable = new DataTable();
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                //Get colummn details
                if (!string.IsNullOrEmpty(firstRowCell.Text))
                {
                    string firstColumn = string.Format("Column {0}", firstRowCell.Start.Column);
                    excelasTable.Columns.Add(hasHeader ? firstRowCell.Text : firstColumn);
                }
            }
            var startRow = hasHeader ? 2 : 1;
            //Get row details
            for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, excelasTable.Columns.Count];
                DataRow row = excelasTable.Rows.Add();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
            }
            //Get everything as generics and let end user decides on casting to required type
            var generatedType = JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(excelasTable));
            return (T)Convert.ChangeType(generatedType, typeof(T));
        }
    }

    public void WriteToExcel(object datas, string path)
    {
        // let's convert our object data to Datatable for a simplified logic.
        // Datatable is the easiest way to deal with complex datatypes for easy reading and formatting.
        var table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(datas), typeof(DataTable))!;
        using var excelPack = new ExcelPackage(path);
        var ws = excelPack.Workbook.Worksheets.Add("ShortageList");
        ws.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
        excelPack.Save();
    }
}

public class ShortageDto
{
    //固定
    public string Number { get; set; }//1,A,Item No.
    public string Description { get; set; }//2,B,Description
    public string Unit { get; set; }//3,C,Unit of Measure Code
    public string SafetyStock { get; set; }//4,D,Safety Stock Quantity
    public string Vendor { get; set; }//5,E,Vendor No.
    public string LeadTime { get; set; }//6,F,Lead Time Calculation
    public string OrderMultiple { get; set; }//7,G,Order Multiple
    public string MainInventory { get; set; }//8,H,Main Location Inventory
    public string WSInventory { get; set; }//9,I,WS Location Inventory
    public string Inventory { get; set; }//10,J,Inventory
    //变化
    public string Shortage { get; set; }//xx,AD,6.2025
    public string BU { get; set; }//xx,AE,
    public string Consumable { get; set; }//xx,AF,

}

public class InventoryString
{
    //固定
    public string Number { get; set; }//A,No.
    public string SafetyStock { get; set; }//E,Safety Stock Quantity
    public string BU { get; set; }//J,Bu code Code
    public string UnitCost { get; set; }//N,Unit Cost
    public string Consumable { get; set; }//AC,Is Consumble Item
}

public class InventoryDto
{
    //固定
    public string Number { get; set; }//A,No.
    public double SafetyStock { get; set; }//E,Safety Stock Quantity
    public string BU { get; set; }//J,Bu code Code
    public double UnitCost { get; set; }//N,Unit Cost
    public bool Consumable { get; set; }//AC,Is Consumble Item
}


//库存估值
public class InventoryValuationString
{
    //固定
    public string Number { get; set; }
    public string Description { get; set; }
    public string Unit { get; set; }
    public string BU { get; set; }
    public string IncreaseQuantity { get; set; }
    public string IncreaseValue { get; set; }
    public string DecreasesQuantity { get; set; }
    public string DecreasesValue { get; set; }
    public string LastQuantity { get; set; }
    public string LastValue { get; set; }
}

public class InventoryValuationDto
{
    //固定
    public string Number { get; set; }
    public string Description { get; set; }//可不要
    public string Unit { get; set; }//可不要
    public string BU { get; set; }//可不要
    public double IncreaseQuantity { get; set; }
    public double IncreaseValue { get; set; }
    public double DecreasesQuantity { get; set; }
    public double DecreasesValue { get; set; }
    public double LastQuantity { get; set; }//可不要
    public double LastValue { get; set; }//可不要
}

